Wednesday, December 16, 2015
SQL Commands
List all Database and their recovery mode
SELECT name, DATABASEPROPERTYEX(name, ‘Recovery’), DATABASEPROPERTYEX(name, ‘Status’) FROM master.dbo.sysdatabases ORDER BY 1
Recover a database from a lost log file
This set of commands will recreate the log file if it is removed. This could cause loss of data if there are things in the log file which have not been committed
EXEC sp_resetstatus [Colleague];
ALTER DATABASE [Colleague] SET EMERGENCY
DBCC checkdb([Colleague])
ALTER DATABASE [Colleague] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ([Colleague], REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE [Colleague] SET MULTI_USER